/**
 * 
 */
package org.andy.dbmove.manager;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author yanwei
 * 
 */
public class TableSplit {

	public List<Table> sqlServerTables() throws SQLException {
		Connection conn = SQL.getConn();
		java.sql.PreparedStatement prsmt = null;
		java.sql.ResultSet rs = null;
		List<Table> lts = new ArrayList<Table>();
		try {
			prsmt = conn.prepareStatement(SQL
					.tableCols(" where d.name in("+queryUseTable(true)+")"));
			rs = prsmt.executeQuery();

			Table tab = null;
			String tmp = null;
			Columns col = null;
			boolean flag = false;
			while (rs.next()) {
				tmp = rs.getString(1);
				if (null != tmp && !"".equals(tmp)) {
					if (null != tab) {
						flag = false;
						lts.add(tab);
					}
						tab = new Table(tmp);
						flag = true;
				}
				col = new Columns(rs.getInt(2), rs.getString(3), (rs.getString(4).equals("1")?true:false),(rs.getString(
						5).equals("1") ? true : false), rs.getString(6),
						rs.getInt(8), (rs.getString(10).equals("1") ? true
								: false), rs.getString(11), rs.getString(12));
				tab.getColumns().add(col);

			}
			if (flag) {
				lts.add(tab);
			}
//		} catch (SQLException e) {

		} finally {
			if (null != rs)
				rs.close();
			if (null != prsmt)
				prsmt.close();
			if (null != conn)
				conn.close();
		}
		return lts;
	}

	/**
	 * 查询有记录的表集合
	 * @param flag true只查有纪录
	 * @return
	 * @throws SQLException 
	 */
	public String queryUseTable(boolean flag) throws SQLException{
		String tables = "select   name  from   sysobjects   where   xType= 'U ' and name <>'dtproperties'    order   by   name   desc";
		Connection conn = null;
		java.sql.PreparedStatement prsmt = null;
		java.sql.PreparedStatement pt = null;
		java.sql.ResultSet rus = null;
		java.sql.ResultSet rs = null;
		String str = "";
		try {
			conn = SQL.getConn();
			prsmt = conn.prepareStatement(tables);
			rs = prsmt.executeQuery();
		String tmp = "";
			while(rs.next()){
				tmp = rs.getString(1);
			
				if(flag){
					pt = conn.prepareStatement("select count(*) from "+tmp); 
					rus = pt.executeQuery();
					rus.next();
					if(rus.getInt(1)>0)
						str+=",'"+tmp+"'";
				}else{
					str+=",'"+tmp+"'";
				}
				
			}
				
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
		}finally{
			try {
				rs.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		return str.substring(1);
	}
//	public static void main(String[] args) {
//		List<Table> l = null;
//		try {
//			l = new TableSplit().sqlServerTables();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}
}
